
use strict;
use XML::LibXML;
use LWP::Simple;
use DBI;
use Config::IniFiles;

# This program read config_cdip.xml, get the spectral wave data file, parse it, then insert the data to the database. It will automatically update platform, sensor,sensor_type,obs_type,m_type,m_scalar_type,uom_type tables if no related record found. But it can't do the unit conversion yet.

#config
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');

my($waveheight,$timeperiod,$direction,$awp,$timedate,@freq,@bandwidth,@energy,@dmean,@a1,@b1,@a2,@b2,@factor,$counter,$organization_row_id,$platform_row_id,$platform_handle,$m_lon,$m_lat);  

#for sqlite database
#my $timevalue="datetime('now')";
#my $dbname="waves.db";
#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
#                    { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}

#for postgres database
#establish database connection
my $dbh = DBI->connect ( "dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {
       die "Cannot connect to database!\n";
       exit 0;
} 


my $timevalue="current_timestamp";

my $missing_value = '-99999';

##################
#read input files for looping through calls
##################

my $target_dir = 'response';

my $config_filepath = 'config_cdip.xml';
my $config = XML::LibXML->new->parse_file($config_filepath);


foreach my $provider ($config->findnodes('//provider')) {
	my $data_url = $provider->findvalue('dataURL'); 
        my $provider_url=$provider->findvalue('url');
	my $startTime = $provider->findvalue('startTime'); 
 	my $endTime = $provider->findvalue('endTime');        
        my $organization=$provider->findvalue('name');
        
	my $cdipId = $provider->findvalue('cdipId');
	$platform_handle = $provider->findvalue('platformHandle');
	my $platform_Id = $provider->findvalue('platformId');
        my $xml_url = $data_url."$cdipId"."+sp";

        if($startTime){
                $xml_url .= "+$startTime"; 
        }
        if($endTime){
                $xml_url .= "+$endTime"; 
        }           

        
        #generate a random file handle concatenating a random number
        my $xml_filepath = "$target_dir/".$platform_Id."latest.txt";
        #print $xml_url."\n";
        my $content = getstore($xml_url, $xml_filepath);
        die "Couldn't get $xml_url" unless defined $content; 
      
	my $sql = qq{ SELECT row_id from organization where short_name like '$organization' };
	#print $sql."\n";
	my $sth = $dbh->prepare( $sql );
	$sth->execute();
	($organization_row_id) = $sth->fetchrow_array;
        
	if ($organization_row_id) {} #print "$organization_name_lkp\n"; 
	else {
		print "organization not found - inserting\n";

		$sql = qq{ SELECT max(row_id) from organization  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($organization_row_id) = $sth->fetchrow_array;	        
                $organization_row_id=$organization_row_id+1;
		$sql = qq{ INSERT into organization(row_id,row_entry_date,short_name,url) values ($organization_row_id,$timevalue,'$organization','$provider_url'); };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
         
	}

	##platform####################
	$sql = qq{ SELECT row_id,short_name,platform_handle from platform where organization_id = $organization_row_id and short_name = '$platform_Id' };
	#print $sql."\n";
	$sth = $dbh->prepare( $sql );
	$sth->execute();
	($platform_row_id) = $sth->fetchrow_array;

	if ($platform_row_id) {
            ;
        } #print "$platform_name_lkp\n"; }
	else {
		print "platform not found - inserting\n";

		#requery for new row_id
		$sql = qq{ SELECT max(row_id) from platform  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($platform_row_id) = $sth->fetchrow_array;
                $platform_row_id++;
                my $undefined_platform_type_id=6;
                #$platform_desc=~ s/,//g;
                my $platform_url=$provider_url."/station_page.php?station=$platform_Id";
		$sql = "INSERT into platform(row_id,row_entry_date,organization_id,type_id,short_name,platform_handle) values ($platform_row_id,$timevalue,$organization_row_id,$undefined_platform_type_id,'$platform_Id','$platform_handle')";
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
        }

        #get row_id from multi_obs table, should set the multi_obs table row_id autoincrement
        
        if($counter==0){
                      $sql="select max(row_id) from multi_obs";
                      $sth = $dbh->prepare($sql);
                      $sth->execute();
                      ($counter) = $sth->fetchrow_array;
                      $counter++;
        }  

 print "counter is $counter\n";
        ##############
        open(FILE,"<$xml_filepath");
        my @data=<FILE>;
        close(FILE);
  
       #parse the file

        my($linecount,$i);
        for($i=0;$i<@data;$i++){
             
             if($data[$i]=~ /File Name/){
                  if($i>0){
                     if($timedate){    insert(); }
                     $waveheight="";$timeperiod="";
                     $direction="";$awp="";
                     @freq=();@bandwidth=();
                     @energy=();
                     @a1=();@b1=();@a2=();@b2=();@factor=();
                  }
                  $linecount=$i;
                  $data[$i]=~ /(.*)(sp\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                  $timedate=substr($3,0,4)."-".substr($3,4,2)."-".substr($3,6,2)." ".substr($3,8,2).":".substr($3,10,2);
             }
             if($i==($linecount+6)){
                 $data[$i]=~ /(Hs\(m\):)(.*)(Tp\(s\):)(.*)(Dp\(deg\):)(.*)(Ta\(s\):)(.*)/;
                 $waveheight=$2;
                 $timeperiod=$4;
                 $direction=$6;
                 $awp=$8;
             }
             if($i==($linecount+2)){
                 $data[$i]=~ /(Location:)\s+(\d.*)\s(N)\s+(\d.*)\s(W)(.*)(Sensor)(.*)/;
                 $m_lat=$2;
                 $m_lon=$4;
                 my @temp=split(/ /,$m_lat);
                 $m_lat=$temp[0]+($temp[1]/60.0);
                 my @temp=split(/ /,$m_lon);
                 $m_lon=$temp[0]+($temp[1]/60.0);
                 $m_lon=sprintf("%.4f",$m_lon);
                 $m_lat=sprintf("%.4f",$m_lat);
                 #print "longitude: **".$m_lon."***\n";
             }
             if($i>($linecount+9)){
                #print "I am bigger than 9$data[$i]";
                 $data[$i]=~ /(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+(\d+.\d+)\s+/;
                 push(@freq,$1);
                 push(@bandwidth,$2);
                 push(@energy,$3);
                 push(@dmean,$4);
                 push(@a1,$5);
                 push(@b1,$6);
                 push(@a2,$7);
                 push(@b2,$8);
                 push(@factor,$9);
             }
        }
       if($i>0){  
	      if($timedate){insert(); }
       }
      
 print "counter is $counter\n";
 print "start tiem is $startTime\n";
 print "end time is $endTime\n";       
} #foreach provider


 $dbh->disconnect();

exit 0;

##############################################

sub escape_literals {

my $platform_url = shift;
$platform_url =~ s/&/&amp;/ ;

return $platform_url;

}


sub getMtype{
     my ($obs_type,$uom_type)=@_;
     my $sql="select m_type.row_id,uom_type.display from m_type 
              left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
              left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
              left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
              where obs_type.standard_name='$obs_type'";
               # print "\n"."$sql\n";
     my $sth = $dbh->prepare($sql);
        $sth->execute();
     my ($m_type_id,$uom_name) = $sth->fetchrow_array;
     $sth->finish();

     if(!$m_type_id){

         #updtae obs_type table
         $sql="select row_id from obs_type where standard_name='$obs_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($obstype_id) = $sth->fetchrow_array;
         if(!$obstype_id){
           $sql="select max(row_id) from obs_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($obstype_id) = $sth->fetchrow_array;
           $obstype_id++;
           $sql="insert into obs_type(row_id,standard_name) values($obstype_id,'$obs_type')";
           $sth = $dbh->prepare($sql);
           print "$sql\n";
           $sth->execute();
           $sth->finish();
          }
           #updtae uom_type table
           $sql="select row_id from uom_type where display='$uom_type'";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($uom_id) = $sth->fetchrow_array;
           if(!$uom_id){
             $sql="select max(row_id) from uom_type";
             $sth = $dbh->prepare($sql);
             $sth->execute();
             my ($uom_id) = $sth->fetchrow_array;
             $uom_id++;
             $sql="insert into uom_type(row_id,standard_name,display) values($uom_id,'$uom_type','$uom_type')";
             $sth = $dbh->prepare($sql);
             print "$sql\n";
             $sth->execute();
             $sth->finish();
            }

        #updtae m_scalar_type table
         $sql="select row_id from m_scalar_type where obs_type_id=$obstype_id and uom_type_id=$uom_id";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($mscalar_id) = $sth->fetchrow_array;
         if(!$mscalar_id){
           $sql="select max(row_id) from m_scalar_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($mscalar_id) = $sth->fetchrow_array;
           $mscalar_id++;
           $sql="insert into m_scalar_type(row_id,obs_type_id,uom_type_id) values($mscalar_id,$obstype_id,$uom_id)";
           print "$sql\n";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           $sth->finish();
          }

                  #update mtype table   
          $sql="select max(row_id) from m_type";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          my ($m_type_id) = $sth->fetchrow_array;
          $m_type_id++;
          $sql="insert into m_type(row_id,num_types,description,m_scalar_type_id) values($m_type_id,1,'$obs_type',$mscalar_id)";
          #print "$sql\n";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          $sth->finish();

     } #end of m_type_id

     return $m_type_id;
}

sub getSensor{
  
    my ($m_type_id,$short_name)=@_;
    my $sql="select row_id from sensor where platform_id=$platform_row_id and m_type_id=$m_type_id";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    # print $sql."\n";
    my ($sensor_id) = $sth->fetchrow_array;
    $sth->finish();
    if(!$sensor_id){
         #insert new record to sensor table
         $sql="select row_id from sensor_type where type_name='$short_name'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($type_id) = $sth->fetchrow_array;
         $sth->finish();
         if(!$type_id){
           $sql="select max(row_id) from sensor_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($type_id) = $sth->fetchrow_array;
           $type_id++;
           $sql="insert into sensor_type(row_id,type_name,description) values($type_id,'$short_name','$short_name')";
           $sth = $dbh->prepare($sql);
           #print "$sql\n";
           $sth->execute();
           $sth->finish();
         }
         $sql="select max(row_id) from sensor";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         ($sensor_id) = $sth->fetchrow_array;
         $sensor_id++;
         $sql="insert into sensor(row_id,row_entry_date,platform_id,type_id,m_type_id,short_name) values ($sensor_id,$timevalue,$platform_row_id,$type_id,$m_type_id,'$short_name')";
          $sth = $dbh->prepare($sql);
          #print "inserting to sensor table $sql\n";
          $sth->execute();
          $sth->finish();
    }
    return $sensor_id;
}


sub insert{
   #timeperiod
   #wavedirection
   #average time period
   #center frequency
   my $myfreq=join(';',@freq);
   #bandwidth
   my $myband=join(';',@bandwidth);
   #energy
   my $myenergy=join(';',@energy);
   #mean direction
   my $mydirection=join(';',@dmean);
   #a1
   my $mya1=join(';',@a1);
   #b1
   my $myb1=join(';',@b1);
   #a2
   my $mya2=join(';',@a2);
   #b2
   my $myb2=join(';',@b2);
   #checkfactor
   my $myfactor=join(';',@factor);
   #waveheight

   my @obstype_array=("significant_wave_height","dominant_wave_period","significant_wave_to_direction","average_wave_period",
"spectral_energy","center_frequencies","fourier_coefficient_a_1","fourier_coefficient_b_1", "fourier_coefficient_a_2","fourier_coefficient_b_2","mean_wave_direction","check_factor");
   my @uom_array=("m","s","deg","s","","","","","","","","");
 my @obs_array=($waveheight,$timeperiod,$direction,$awp,$myenergy,$myfreq,$mya1,$myb1,$mya2,$myb2, $mydirection,$myfactor);

for (my $m=0;$m<@obstype_array;$m++){
   #print "\n$obstype_array[$m]\n\n";
   my $m_type_id=getMtype($obstype_array[$m],$uom_array[$m]);
   my $sensor_id=getSensor($m_type_id,$obstype_array[$m]);
        
   my $sql="select row_id from multi_obs where m_date='$timedate' and sensor_id=$sensor_id and m_type_id=$m_type_id";
   
   my $sth = $dbh->prepare($sql);
   $sth->execute();
   my ($row_id) = $sth->fetchrow_array;
   $sth->finish();

   if($row_id>0){;}
   else {
                        
         if($m<4){
             $sql="INSERT into multi_obs(row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,$obs_array[$m])";
              
         }
         else {
             $sql="INSERT into multi_obs(row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value_list) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,'$obs_array[$m]')";
         }
         #print $sql."\n";
             $sth = $dbh->prepare($sql);         
             $sth->execute();
             $sth->finish();
             $counter++;
    }

}
   
}
